//
//  eiaCrudeOilPrice.m
//  MarketDataColloctor
//
//  Created by jiro on 10/10/17.
//  Copyright 2010 jiro music. All rights reserved.
//

#import "EiaCrudeOilSpotPriceCollector.h"
#import "WebResourceCollector.h"
#import "../../../Commons/DbOperator.h"
#import "../../../DbTypes/DbUtility.h"
#import "../../../DbTypes/DbConstants.h"
#import "eiaHtmlParser.h"

@implementation EiaCrudeOilSpotPriceCollector


+ (void) collect
{
	//	---- get html
	NSError* error = nil;
	id collector = [ [ WebResourceCollector alloc ] initWithEncoding: NSUTF8StringEncoding ];
	id html = nil;
//	id html = [ NSString stringWithContentsOfFile: @"a_1.html" encoding: NSUTF8StringEncoding error: & error ];
	if ( html == nil ) {
		id url = [ NSURL URLWithString: @"http://www.eia.gov/dnav/pet/pet_pri_wco_k_w.htm" ];
		id urls = [ NSArray arrayWithObject: url ];
		id htmls = [ NSMutableArray array ];
		[ collector collectContentsOf: urls intoStrings: htmls ];
		html = [ htmls objectAtIndex: 0 ];
		[ html writeToFile: @"a_1.html" atomically: YES encoding: NSUTF8StringEncoding error: & error ];
	}
//	NSLog( @"%@", html );
	//	----

	//	---- parse html, get country, type, link url
	NSXMLDocument* document = [ [ NSXMLDocument alloc ] initWithXMLString: html options:NSXMLDocumentTidyHTML error: & error ];
	if ( document == nil ) {
		NSLog( @"xml parse error: %@", document, error );
		return;
	}
	
	id element = [ document rootElement ];
	id nodes = [ element nodesForXPath: @"/html/body/form/table[@class=\"data1\"]/tr[@class=\"DataRow\"]"  error: & error ];	
	id node;
	id e = [ nodes objectEnumerator ];
	id historyUrls = [ NSMutableArray array ];
	id countryNames = [ NSMutableArray array ];
	id crudeTypeNames = [ NSMutableArray array ];
	while ( node = [ e nextObject ] ) {

		// ---- get cuuntry name and crude type
//		NSLog( @"%@", node );
		id nodes = [ node nodesForXPath: @"td[@class=\"DataStub\"]/table/tr/td[@class=\"DataStub1\"]/b" error: & error ];
		if ( [ nodes count ] == 0 ) {
			nodes = [ node nodesForXPath: @"td[@class=\"DataStub\"]/table/tr/td[@class=\"DataStub1\"]" error: & error ];
		}
		id thisNode = [ nodes objectAtIndex: 0 ];
		id text = [ thisNode stringValue ];
		text = [ text stringByReplacingOccurrencesOfString: @"*" withString: @"" ];
//		NSLog( @"%@", text );

		id countryName;
		id crudeTypeName;
		
		if ( [ text rangeOfCharacterFromSet: [ NSCharacterSet characterSetWithCharactersInString: @"," ] ].location != NSNotFound ) {
			id tokens = [ text componentsSeparatedByString: @"," ];
			countryName = [ tokens objectAtIndex: 0 ];
			crudeTypeName = [ tokens objectAtIndex: 1 ];
		} else {
			countryName = text;
			crudeTypeName = @"";
		}
		countryName = [ countryName stringByTrimmingCharactersInSet: [ NSCharacterSet whitespaceAndNewlineCharacterSet ] ];
		crudeTypeName = [ crudeTypeName stringByTrimmingCharactersInSet: [ NSCharacterSet whitespaceAndNewlineCharacterSet ] ];
//		NSLog( @"%@, %@", countryName, crudeTypeName );
		//	----
		
		//	---- get histry url
		nodes = [ node nodesForXPath: @"td[@class=\"DataHist\"]/a" error: & error ];
		thisNode = [ nodes objectAtIndex: 0 ];
		id urlString = [ NSString stringWithFormat:
			@"http://www.eia.gov/dnav/pet/%@",
			[ [ thisNode attributeForName: @"href" ] stringValue ] ];
		urlString = [ urlString stringByReplacingOccurrencesOfString: @"./" withString: @"" ];
		id historyUrl = [ NSURL URLWithString: urlString ];
//		NSLog( @"%@", histroyUrl );
		//	----
		
		[ historyUrls addObject: historyUrl ];
		[ countryNames addObject: countryName ];
		[ crudeTypeNames addObject: crudeTypeName ];

//		NSLog( @"%d", [ nodes count ] );
//		for ( int i = 0; i < [ nodes count ]; i++ ) {
//			NSLog( @"%@", [ nodes objectAtIndex: i ] );
//		}
	}
	// ----

	//	---- process each html
	id dbOperator = [ DbOperator defaultOperator ];
	for ( int i = 0; i < [ historyUrls count ]; i++ ) {

		id countryName = [ countryNames objectAtIndex: i ];
		id crudeTypeName = [ crudeTypeNames objectAtIndex: i ];
		if ( [ countryName isEqualToString: @"Venezeula" ] ) {
			countryName = @"Venezuela";
		}
		
		//	---- get country code
		PGresult* res;
		id sql = [ NSString stringWithFormat: @"select %@ from %@ where %@ = '%@'",
			kText_Code, kText_CrudeOilProductCountryTable, kText_Name, countryName ];
		if ( DbResponse_Success != [ dbOperator executeQuery: sql intoResult: & res ] )
		{
			NSLog( @"sql fail, message: %@, sql: %@", [ dbOperator errorMessage ], sql );
			continue;
		}
		int countryCode = -1;
		if ( PQnfields( res ) == 0 || PQntuples( res ) == 0 ) {
	
			PQclear( res );
	
			//	add entry
			sql = [ NSString stringWithFormat: @"select %@ from %@ order by %@ limit 1;",
					kText_Code, kText_CrudeOilProductCountryTable, kText_Code ];
			if ( DbResponse_Success != [ dbOperator executeQuery: sql intoResult: & res ] ) {
				NSLog( @"sql fail, message: %@, sql: %@", [ dbOperator errorMessage ], sql );
				continue;
			}
			countryCode = [ DbUtility parseAsDouble: PQgetvalue( res, 0, 0) ] + 1;
			
			PQclear( res );
			sql = [ NSString stringWithFormat: @"insert into %@ values ( %d, '%@' );",
				kText_CrudeOilProductCountryTable, countryCode, countryName ];
			if ( DbResponse_Success != [ dbOperator executeQuery: sql intoResult: & res ] ) {
				NSLog( @"sql fail, message: %@, sql: %@", [ dbOperator errorMessage ], sql );
				continue;				
			}
			PQclear( res );
			
			NSLog( @"%@", sql );

		} else {
			countryCode = [ DbUtility parseAsDouble: PQgetvalue( res, 0, 0 ) ];
			PQclear( res );
		}
		//	----
		
		//	---- get crude type
		int crudeType = -1;
		sql = [ NSString stringWithFormat: @"select %@ from %@ where %@ = '%@'",
			kText_Code, kText_CrudeOilCodeTable, kText_Name, crudeTypeName ];
		res = nil;
		if ( DbResponse_Success != [ dbOperator executeQuery: sql intoResult: & res ] ) {
			NSLog( @"sql fail, message: %@, sql: %@", [ dbOperator errorMessage ], sql );
			continue;
		}
		if ( PQnfields( res ) == 0 || PQntuples( res ) == 0 ) {
	
			PQclear( res );
	
			//	add entry
			sql = [ NSString stringWithFormat: @"select %@ from %@ order by %@ limit 1;",
					kText_Code, kText_CrudeOilProductCountryTable, kText_Code ];
			if ( DbResponse_Success != [ dbOperator executeQuery: sql intoResult: & res ] ) {
				NSLog( @"sql fail, message: %@, sql: %@", [ dbOperator errorMessage ], sql );
				continue;
			}
			crudeType = [ DbUtility parseAsDouble: PQgetvalue( res, 0, 0) ] + 1;
			
			PQclear( res );
			sql = [ NSString stringWithFormat: @"insert into %@ values ( %d, '%@' );",
				kText_CrudeOilCodeTable, crudeType, crudeTypeName ];
			if ( DbResponse_Success != [ dbOperator executeQuery: sql intoResult: & res ] ) {
				NSLog( @"sql fail, message: %@, sql: %@", [ dbOperator errorMessage ], sql );
				continue;
			}
			PQclear( res );
			
			NSLog( @"%@", sql );

		} else {
			crudeType = [ DbUtility parseAsDouble: PQgetvalue( res, 0, 0 ) ];
			PQclear( res );
		}
		//	----

		//	---- get latest date for country and crude type
		sql = [ NSString stringWithFormat: @"select %@ from %@ where %@ = %d and %@ = %d order by %@ desc limit 1;",
			kText_ExchangeDate, kText_CrudeOilExchangeTable,
			kText_CrudeOilProductCountryCode, countryCode, kText_CrudeOilCode, crudeType,
			kText_ExchangeDate ];
		if ( DbResponse_Success != [ dbOperator executeQuery: sql intoResult: & res ] ) {
			NSLog( @"sql fail, message: %@, sql: %@", [ dbOperator errorMessage ], sql );
			continue;
		}
//		NSLog( @"%@", sql );
		NSDate* latestDate = nil;
		if ( PQnfields( res ) != 0 && PQntuples( res ) != 0 ) {
			latestDate = [ DbUtility parseAsNSDate: PQgetvalue( res, 0, 0 ) ];
		}
		PQclear( res );
		//	----
		
		id htmls = [ NSMutableArray array ];
		id urls = [ NSArray arrayWithObject: [ historyUrls objectAtIndex: i ] ];
		[ collector collectContentsOf: urls intoStrings: htmls ];

		id html = [ htmls objectAtIndex: 0 ];
		id results = [ NSMutableDictionary dictionary ];
		if ( NO == [ EiaHtmlParser parseDataHtml: html dataInterval: Weekly latestDate: latestDate result: results ] ) {
			NSLog( @"fail to parse html country: %@, type: %@", 
				[ countryNames objectAtIndex: i ], [ crudeTypeNames objectAtIndex: i  ] );
			continue;
		}
		if ( [ results count ] == 0 ) {
			NSLog( @"get no data from html, country: %@, type: %@", 
				[ countryNames objectAtIndex: i ], [ crudeTypeNames objectAtIndex: i  ] );
			continue;
		}
		
		bool valueAdded = NO;
		sql = [ NSString stringWithFormat: @"insert into %@ values\n", kText_CrudeOilExchangeTable ];
		NSEnumerator* e = [ results keyEnumerator ];
		NSDate* date;
		while ( date = [ e nextObject ] ) {
			//	---- check the former condition
			id value = [ results objectForKey: date ];
			if ( [ value isEqualToString: @"NA" ] ) {
				continue;
			}
			//	----

			if ( valueAdded ) {
				sql = [ sql stringByAppendingString: @", " ];
			} else {
				valueAdded = YES;
			}
			sql = [ sql stringByAppendingFormat: @"( %d, %d, '%@', %@ )\n", 
				countryCode, crudeType, date, value ];
		}
		sql = [ sql stringByAppendingString: @";" ];
		//	 ----
		
		if ( valueAdded == NO ) {
			continue;
		}

		//	 ---- insert data to table 
		if ( DbResponse_Success != [ dbOperator executeQuery: sql intoResult: & res ] ) {
			NSLog( @"sql failed, message: %@, sql: %@", [ dbOperator errorMessage ], sql );
		}
		PQclear( res );
		//	----
		
		NSLog( @"done: %@(%d), %@(%d), data count %d", countryName, countryCode, crudeTypeName, crudeType, [ results count ] );
	}
	
	
	[ dbOperator disconnect ];
	//	----
	

	//	create sql
	
	//	insert data
}

@end
